﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using Estimate.Common.DBFieldTypes;
using Estimate.Common.Entity;

namespace Estimate.Common.Repository
{
    /// <summary>
    /// 排班表
    /// </summary>
    public class ScheduleRepository : BaseRepository
    {
        public ScheduleRepository(string connectionString)
            : base(connectionString, "New_Schedule")
        { }

        public override string GetCreateTableSql()
        {
            string sqlcreate = String.Format("CREATE TABLE [{0}] (id bigint identity(1,1), type varchar(8),realdate date,  stations varchar(1000), clock varchar(8), span varchar(9), sendpoint varchar(8), forecastnumber text) \n", _tableName);
            sqlcreate += String.Format("CREATE CLUSTERED INDEX [{0}_INDEX] ON [{1}] (type,realdate, clock, span, sendpoint) \n", _tableName, _tableName);
            sqlcreate += String.Format("alter table [{0}] add primary key (id)", _tableName);
            return sqlcreate;
        }

        public void InsertRecord(ScheduleSpanKind type, string stations, DateTime realdate, ForeClockType clock, ForeSpanType span, ForeSendpointType sendpoint,string forecastnumber)
        {
            string sql = String.Format("INSERT INTO [{0}] (type,realdate,clock,span,sendpoint,forecastnumber,stations) values(@type,@realdate,@clock,@span,@sendpoint,@forecastnumber,@stations)", _tableName);
            SqlParameter[] param = new SqlParameter[7];
            param[0] = new SqlParameter("@type", type.ToString());
            param[1] = new SqlParameter("@stations", stations);
            param[2] = new SqlParameter("@realdate", realdate.ToString("yyyy-MM-dd"));
            param[3] = new SqlParameter("@clock", clock.ToString());
            param[4] = new SqlParameter("@span", span.ToString());
            param[5] = new SqlParameter("@sendpoint", sendpoint.ToString());
            param[6] = new SqlParameter("@forecastnumber", forecastnumber);
            DBHelper.ExecuteSql(_connectionString, sql, param);
        }

        public void UpdateRecord(ScheduleSpanKind type, string stations, DateTime realdate, ForeClockType clock, ForeSpanType span, ForeSendpointType sendpoint, string forecastnumber)
        {
            string sql = String.Format("UPDATE  [{0}] SET forecastnumber = @forecastnumber WHERE type=@type  and realdate=@realdate and span=@span and clock=@clock and sendpoint=@sendpoint and stations = @stations ", _tableName);
            SqlParameter[] param = new SqlParameter[7];
            param[0] = new SqlParameter("@type", type.ToString());
            param[1] = new SqlParameter("@stations", stations);
            param[2] = new SqlParameter("@realdate", realdate.ToString("yyyy-MM-dd"));
            param[3] = new SqlParameter("@clock", clock.ToString());
            param[4] = new SqlParameter("@span", span.ToString());
            param[5] = new SqlParameter("@sendpoint", sendpoint.ToString());
            param[6] = new SqlParameter("@forecastnumber", forecastnumber);
            DBHelper.ExecuteSql(_connectionString, sql, param);
        }

        public bool IsRecordExist(ScheduleSpanKind type, string stations,  DateTime realdate, ForeClockType clock, ForeSpanType span, ForeSendpointType sendpoint)
        {
            string sql = String.Format("SELECT id FROM [{0}] WHERE type=@type  and realdate=@realdate and span=@span and clock=@clock and sendpoint=@sendpoint and stations=@stations",_tableName);
            SqlParameter[] param = new SqlParameter[6];
            param[0] = new SqlParameter("@type", type.ToString());
            param[1] = new SqlParameter("@stations", stations);
            param[2] = new SqlParameter("@realdate", realdate.ToString("yyyy-MM-dd"));
            param[3] = new SqlParameter("@clock", clock.ToString());
            param[4] = new SqlParameter("@span", span.ToString());
            param[5] = new SqlParameter("@sendpoint", sendpoint.ToString());
            return DBHelper.Exists(_connectionString, sql, param);
        }

        public void SetRecord(ScheduleSpanKind type, string stations, DateTime realdate, ForeClockType clock, ForeSpanType span, ForeSendpointType sendpoint,string forecastnumber)
        {
            if( IsRecordExist(type,stations,realdate,clock,span,sendpoint))
                UpdateRecord(type,stations,realdate,clock,span,sendpoint,forecastnumber);
            else
                InsertRecord(type, stations, realdate, clock, span, sendpoint, forecastnumber);
        }

        public void DeleteRecordWithDateRange(DateTime beginDate, DateTime endDate)
        {
            string sql = String.Format("DELETE FROM [{0}] WHERE realdate >= '{1}' and realdate <='{2}'",
               new object[]{
                    _tableName,
                    beginDate.ToString("yyyy-MM-dd"),
                    endDate.ToString("yyyy-MM-dd")
                });
            DBHelper.ExecuteSql(_connectionString, sql);
        }

        public ScheduleEntity[] GetSchedulesWithDateRange(ScheduleType type, DateTime beginDate, DateTime endDate, ForeClockType[] clocks, ForeSpanType span, ForeSendpointType[] sendpoints, string stations)
        {
            CacheClient.KeyPrefix = this._tableName + "GetSchedulesWithDateRange";
            List<object> ps = new List<object>() { type, beginDate, endDate, span, stations };
            foreach (ForeClockType clock in clocks)
            {
                ps.Add(clock);
            }
            foreach (ForeSendpointType sendpoint in sendpoints)
            {
                ps.Add(sendpoint);
            }
            string cacheKey = getValideCacheKey(ps);
            object cache = CacheClient.Get(cacheKey);

            if (cache == null)
            {

                List<ScheduleEntity> result = new List<ScheduleEntity>();
                string sql = String.Format("SELECT  * FROM [{0}] WHERE type='{1}'  and realdate >= '{2}' and realdate <= '{3}' and span='{4}' and clock in ({5}) and sendpoint in ({6}) and stations = '{7}' ORDER BY realdate",
                        new object[] { _tableName, type.ToString(), 
                                    beginDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"), 
                                    span.ToString(), this.makeClocksDBIn(clocks),this.makeSendpointsDBIn(sendpoints),
                                    stations
                    }
                    );

                SqlConnection connection = new SqlConnection(_connectionString);
                SqlDataReader reader = DBHelper.ExecuteReader(connection, sql);
                while (reader.Read())
                {
                    ScheduleEntity entity = new ScheduleEntity();
                    entity.id = Convert.ToInt32(reader["id"]);
                    entity.Stations = reader["stations"].ToString();
                    entity.ForecastNumber = reader["forecastnumber"].ToString();
                    entity.Clock = (ForeClockType)Enum.Parse(typeof(ForeClockType), reader["clock"].ToString());
                    entity.Sendpoint = (ForeSendpointType)Enum.Parse(typeof(ForeSendpointType), reader["sendpoint"].ToString());
                    entity.Span = (ForeSpanType)Enum.Parse(typeof(ForeSpanType), reader["span"].ToString());
                    entity.type = (ScheduleType)Enum.Parse(typeof(ScheduleType), reader["type"].ToString());
                    entity.RealDate = DateTime.Parse(reader["realdate"].ToString());
                    result.Add(entity);
                }
                connection.Close();
                CacheClient.Set(cacheKey, result.ToArray());
                return result.ToArray();
            }
            else
            {
                ScheduleEntity[] result = cache as ScheduleEntity[];
                return result;
            }
        }

        public ScheduleEntity[] GetSchedulesWithDateRange(ScheduleType type, DateTime beginDate, DateTime endDate, ForeClockType[] clocks, ForeSendpointType[] sendpoints, string stations)
        {
            CacheClient.KeyPrefix = this._tableName + "GetSchedulesWithDateRange2";
            List<object> ps = new List<object>() { type, beginDate, endDate, stations };
            foreach (ForeClockType clock in clocks)
            {
                ps.Add(clock);
            }
            foreach (ForeSendpointType sendpoint in sendpoints)
            {
                ps.Add(sendpoint);
            }
            string cacheKey = getValideCacheKey(ps);
            object cache = CacheClient.Get(cacheKey);

            if (cache == null)
            {

                List<ScheduleEntity> result = new List<ScheduleEntity>();
                string sql = String.Format("SELECT  * FROM [{0}] WHERE type='{1}'  and realdate >= '{2}' and realdate <= '{3}' and span <> '' and clock in ({4}) and sendpoint in ({5}) and stations = '{6}' ORDER BY realdate",
                        new object[] { _tableName, type.ToString(), 
                                    beginDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"), 
                                    this.makeClocksDBIn(clocks),this.makeSendpointsDBIn(sendpoints),
                                    stations
                    }
                    );

                SqlConnection connection = new SqlConnection(_connectionString);
                SqlDataReader reader = DBHelper.ExecuteReader(connection, sql);
                while (reader.Read())
                {
                    ScheduleEntity entity = new ScheduleEntity();
                    entity.id = Convert.ToInt32(reader["id"]);
                    entity.Stations = reader["stations"].ToString();
                    entity.ForecastNumber = reader["forecastnumber"].ToString();
                    entity.Clock = (ForeClockType)Enum.Parse(typeof(ForeClockType), reader["clock"].ToString());
                    entity.Sendpoint = (ForeSendpointType)Enum.Parse(typeof(ForeSendpointType), reader["sendpoint"].ToString());
                    entity.Span = (ForeSpanType)Enum.Parse(typeof(ForeSpanType), reader["span"].ToString());
                    entity.type = (ScheduleType)Enum.Parse(typeof(ScheduleType), reader["type"].ToString());
                    entity.RealDate = DateTime.Parse(reader["realdate"].ToString());
                    result.Add(entity);
                }
                connection.Close();
                CacheClient.Set(cacheKey, result.ToArray());
                return result.ToArray();
            }
            else
            {
                ScheduleEntity[] result = cache as ScheduleEntity[];
                return result;
            }
        }

        public ScheduleEntity[] GetSchedulesWithDateRage(ScheduleType type, DateTime beginDate, DateTime endDate, ForeSpanType span, string stations)
        {
            CacheClient.KeyPrefix = this._tableName + "GetSchedulesWithDateRage3";
            List<object> ps = new List<object>() { type, beginDate, endDate, span, stations };
            string cacheKey = getValideCacheKey(ps);
            object cache = CacheClient.Get(cacheKey);

            if (cache == null)
            {

                List<ScheduleEntity> result = new List<ScheduleEntity>();
                string sql = String.Format("SELECT  * FROM [{0}] WHERE type='{1}'  and  realdate >= '{2}' and realdate <= '{3}' and span='{4}' and clock <> '' and sendpoint <> '' and stations = '{5}' ORDER BY realdate",
                        new object[] { _tableName, type.ToString(), 
                                    beginDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"), 
                                    span.ToString(),
                                    stations
                    }
                    );

                SqlConnection connection = new SqlConnection(_connectionString);
                SqlDataReader reader = DBHelper.ExecuteReader(connection, sql);
                while (reader.Read())
                {
                    ScheduleEntity entity = new ScheduleEntity();
                    entity.id = Convert.ToInt32(reader["id"]);
                    entity.Stations = reader["stations"].ToString();
                    entity.ForecastNumber = reader["forecastnumber"].ToString();
                    entity.Clock = (ForeClockType)Enum.Parse(typeof(ForeClockType), reader["clock"].ToString());
                    entity.Sendpoint = (ForeSendpointType)Enum.Parse(typeof(ForeSendpointType), reader["sendpoint"].ToString());
                    entity.Span = (ForeSpanType)Enum.Parse(typeof(ForeSpanType), reader["span"].ToString());
                    entity.type = (ScheduleType)Enum.Parse(typeof(ScheduleType), reader["type"].ToString());
                    entity.RealDate = DateTime.Parse(reader["realdate"].ToString());
                    result.Add(entity);
                }
                connection.Close();

                CacheClient.Set(cacheKey, result.ToArray());
                return result.ToArray();
            }
            else
            {
                ScheduleEntity[] result = cache as ScheduleEntity[];
                return result;
            }
        }

        #region tyh 2013-12-11 创建
        public ScheduleEntity[] GetSchedulesWithDateRange( DateTime beginDate, DateTime endDate, ForeClockType[] clocks, ForeSendpointType[] sendpoints)
        {
            //CacheClient.KeyPrefix = this._tableName + "GetSchedulesWithDateRange4";
            //List<object> ps = new List<object>() { beginDate, endDate };
            //foreach (ForeClockType clock in clocks)
            //    ps.Add(clock);

            //foreach (ForeSendpointType sendpoint in sendpoints)
            //    ps.Add(sendpoint);
          
            //string cacheKey = getValideCacheKey(ps);
            //object cache = CacheClient.Get(cacheKey);

            //if (cache == null)
            //{

                List<ScheduleEntity> result = new List<ScheduleEntity>();
                string sql = String.Format("SELECT  * FROM [{0}] WHERE realdate >= '{1}' and realdate <= '{2}'  and clock in ({3}) and sendpoint in ({4})  ORDER BY realdate",
                        new object[] { _tableName,
                                       beginDate.ToString("yyyy-MM-dd"),
                                       endDate.ToString("yyyy-MM-dd"), 
                                       this.makeClocksDBIn(clocks),
                                       this.makeSendpointsDBIn(sendpoints)
                                    
                    }
                    );

                SqlConnection connection = new SqlConnection(_connectionString);
                SqlDataReader reader = DBHelper.ExecuteReader(connection, sql);
                while (reader.Read())
                {
                    ScheduleEntity entity = new ScheduleEntity();
                    entity.id = Convert.ToInt32(reader["id"]);
                    entity.Station = reader["station"].ToString();
                    entity.ForecastNumber = reader["forecastnumber"].ToString();
                    entity.Clock = (ForeClockType)Enum.Parse(typeof(ForeClockType), reader["clock"].ToString());
                    entity.Sendpoint = (ForeSendpointType)Enum.Parse(typeof(ForeSendpointType), reader["sendpoint"].ToString());
                    //entity.Span = (ForeSpanType)Enum.Parse(typeof(ForeSpanType), reader["span"].ToString());
                    entity.SpanKind = (ScheduleSpanKind)Enum.Parse(typeof(ScheduleSpanKind), reader["span_kind"].ToString());
                    entity.RealDate = DateTime.Parse(reader["realdate"].ToString());
                    result.Add(entity);
                }
                connection.Close();
                //CacheClient.Set(cacheKey, result.ToArray());
                return result.ToArray();
            //}
            //else
            //{
            //    ScheduleEntity[] result = cache as ScheduleEntity[];
            //    return result;
            //}
        }

        /// <summary>
        /// 获取每个时间段内各站点的排班表记录数
        /// </summary>
        /// <param name="beginDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
        public Dictionary<string ,int> GetScheduleCountWithDateRange(DateTime beginDate, DateTime endDate)
        {
            //CacheClient.KeyPrefix = this._tableName + "GetScheduleCountWithDateRange";
            //List<object> ps = new List<object>() { beginDate, endDate };
            //string cacheKey = getValideCacheKey(ps);
            //object cache = CacheClient.Get(cacheKey);

            //if (cache == null)
            //{

                Dictionary<string, int> result = new Dictionary<string, int>();

                string sql = String.Format("SELECT station,count(station) as count FROM [{0}] WHERE realdate >= '{1}' and realdate <= '{2}' GROUP BY station",
                        new object[] { _tableName,
                                       beginDate.ToString("yyyy-MM-dd"),
                                       endDate.ToString("yyyy-MM-dd")
                                    
                    }
                    );

                SqlConnection connection = new SqlConnection(_connectionString);
                SqlDataReader reader = DBHelper.ExecuteReader(connection, sql);
                while (reader.Read())
                    result.Add(reader["station"].ToString(), (int)reader["count"]);
                
                connection.Close();
                //CacheClient.Set(cacheKey, result);
                return result;
            //}
            //else
            //{
            //    Dictionary<string, int> result = cache as Dictionary<string, int>;
            //    return result;
            //}
        }

        #endregion

        public void UpdateRecord(string id, string forecastnumber)
        {
            string sql = String.Format(" UPDATE {0} set forecastnumber=@forecastnumber   WHERE id in ( SELECT  a.id	FROM {0} a, {0} b	WHERE a.type = b.type and a.realdate = b.realdate and b.clock = a.clock and a.sendpoint = b.sendpoint and a.stations = b.stations 	and b.id = @id 	) ", _tableName);
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@id", id);
            param[1] = new SqlParameter("@forecastnumber", forecastnumber);
            DBHelper.ExecuteSql(_connectionString, sql, param);
        }

        private List<SqlParameter[]> _paramList = new List<SqlParameter[]>();

        public void PrepareGroupInsert()
        {
            _paramList.Clear();
        }

        public void AddItemToGroup(ScheduleSpanKind type, string stations, DateTime realdate, ForeClockType clock, ForeSpanType span, ForeSendpointType sendpoint, string forecastnumber)
        {
            SqlParameter[] param = new SqlParameter[7];
            param[0] = new SqlParameter("@type", type.ToString());
            param[1] = new SqlParameter("@stations", stations);
            param[2] = new SqlParameter("@realdate", realdate.ToString("yyyy-MM-dd"));
            param[3] = new SqlParameter("@clock", clock.ToString());
            param[4] = new SqlParameter("@span", span.ToString());
            param[5] = new SqlParameter("@sendpoint", sendpoint.ToString());
            param[6] = new SqlParameter("@forecastnumber", forecastnumber);
            _paramList.Add(param);
        }

        public void DoGroupInsert()
        {
            string sql = String.Format("INSERT INTO [{0}] (type,realdate,clock,span,sendpoint,forecastnumber,stations) values(@type,@realdate,@clock,@span,@sendpoint,@forecastnumber,@stations)", _tableName);
            DBHelper.ExecuteSqlList(_connectionString, sql, _paramList);
        }
    }
}
